# -*- coding:utf-8 -*-
from xlwt import *
import xlrd

import pymysql
conn = pymysql.connect(host='xxx',user='root', passwd='xxx', db='xxx', charset='utf8')
cur = conn.cursor()
def SQL(cur,sql):
    cur.execute(sql);
    return (cur);

a=SQL(cur, r"SELECT a.f_nickname,b.f_name,c.f_stockcode,c.f_stockname,c.f_operate,c.f_createtime FROM (SELECT f_id,f_nickname FROM user.t_user) a JOIN (SELECT f_userid,f_name,f_account FROM unitrade_gentou.t_gentou_product WHERE f_status='Y' AND f_userid NOT IN ('77','79','196','150','80','81','713','72','2844','1855','418','2787','7')) b ON a.f_id=b.f_userid JOIN (SELECT f_account,f_stockcode,f_stockname,f_operate,f_createtime FROM unitrade_counter.t_position_statistics WHERE DATE_FORMAT(f_createtime,'%Y-%m-%d')=DATE_SUB(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 0 DAY) AND f_operate IN ('U','D')) c ON b.f_account=c.f_account;")
w= Workbook(encoding='utf-8')
ws= w.add_sheet(u"抓涨停")
i=1

ff = ['理财师昵称', '理财服务名称', '股票代码', '股票名称', '涨跌停', '时间']

g = 0
for x in ff:
	fnt = Font()
	style = XFStyle()
	style.font = fnt
	ws.write(0, g, x)
	ws.row(i).set_style(style)
	g = g + 1

try:
    for b in a:
        fnt = Font()
        style = XFStyle()
        style.font = fnt
        for f in range(0,len(b)):
            ws.write(i, f, '%s' %b[f])
            ws.row(i).set_style(style)
        i = i + 1
    if b:
        w.save(u"抓涨停.xls")
except Exception as e:
    print(e)

cur.close()
conn.close()


